Overview:
To Query a MySQL database server using a Python Program the client machine needs to have a database client API. There are several client APIs to choose from and the focus here is how to use PyMySQL to connect to a MySQL database server and query a database table.
PyMySQL is a MySQL Client written entirely using the Python Programming Language. PyMySQL works with the Python implementations - CPython, PyPy and IronPython.
Steps required to query a MySQL database using PyMySQL:
- Using PyMySQL Module a Connection Object needs to be created.
- A connection object can be created by passing the destination database server, user name on the database server, associated password and the database name.
- A cursor type can be specified as well so that results can be retrieved in a way preferred by the developer. For Example, specifying the cursor type as pymysql.cursors.DictCursor the results of a query can be obtained as name value pairs - with column name as name to access the database cell value.
- Once a connection object is obtained the next step is to create a cursor object.
- Using the Cursor object a SQL query can be executed.
- By making a fetchall() call on cursor object, the SQL query results to the MySQL database can be retrieved into a Python dictionary.
- Doing a dictionary lookup with the column names can retrieve the value of individual columns in a row.
Example:
|
# import the mysql client for python import pymysql
# Create a connection object dbServerName = "127.0.0.1" dbUser = "root" dbPassword = "" dbName = "test" charSet = "utf8mb4" cusrorType = pymysql.cursors.DictCursor
connectionObject = pymysql.connect(host=dbServerName, user=dbUser, password=dbPassword, db=dbName, charset=charSet,cursorclass=cusrorType) try:
# Create a cursor object cursorObject = connectionObject.cursor()
# SQL query string sqlQuery = "select * from Student"
# Execute the sqlQuery cursorObject.execute(sqlQuery)
#Fetch all the rows rows = cursorObject.fetchall()
for row in rows: print(row["id"]) print(row["firstname"]) print(row["lastname"]) print(row["courseid"]) except Exception as e: print("Exeception occured:{}".format(e)) finally: connectionObject.close() |
Output:
|
1 John Adams 154 2 Jefferson Thomas 154 3 Ada Lovelace 154 4 Alan Turing 154 |